const express = require('express');

const routes = express.Router();

//引入mysql
const mysql = require('mysql');

//引入util模块
var util = require('util');
//创建连接池
var pool = mysql.createPool({
    connectionLimit : 10,
    host : 'localhost',
    user : 'root',
    password : 'root',
    database : 'stumsg'
})

routes.route('/')
    .get( (req,resp)=>{

        //获取cookies
        var uid = req.cookies.uid;
        var gender = req.cookies.gender;

        //判断
        if(gender == 1){
            var pname = '教师';
        }else {
            var pname = '学生';

        }

        resp.render('add_scores',{
            uid : uid,
            pname : pname,
        })
    } )
    //模块化路由
    //查询当前学期的全部课程
    routes.post('/sel_class',(req,resp)=>{
        //获取Ajax提交过来的数据  做专业对比较
        var sel = req.body.nums;
        //console.log(sel)
        //    获取cookies  当前教师专业的id
        var sub_id = req.cookies.sub_id;
        //    sql语句
        var sql = `SELECT id , class_name FROM sub_class WHERE num = ${sel} AND sub_id = ${sub_id}`;
        //    执行sql语句
        pool.getConnection( (err , connection)=>{
            if (err) throw err;
            connection.query( sql, (error , result)=>{
                //    释放连接
                connection.release();

                //ajax的数据是直接传递给html的  所以不需要渲染模板和值对值的传递方式
                resp.send( result );

                if(error) throw error;
                } )
        } )
    } )

//查询有哪些学生是没有录入成绩的
//模块化路由
routes.post('/sel_name',(req,resp)=>{
    //获取Ajax提交过来的数据
    //当前学期号
    var numid = req.body.numid;
    //console.log(sel)
    //    获取cookies  当前教师专业的id
    var sub_id = req.cookies.sub_id;
    //当前的学期的课程id号
    var id = req.body.id;
    //console.log(numid , sub_id , id)
    //    sql语句
    var sql = `SELECT uid , stu_name FROM stu_list WHERE sub_id = ${sub_id} AND uid NOT IN (SELECT scores.uid FROM scores LEFT JOIN sub_class ON scores.class_id = sub_class.id WHERE sub_class.sub_id = ${sub_id} AND sub_class.num = ${numid} AND sub_class.id = ${id})`;
    //console.log(sql)
    //    执行sql语句
    pool.getConnection( (err , connection)=>{
        if (err) throw err;
        connection.query( sql, (error , result)=>{
            //    释放连接
            connection.release();

            //ajax的数据是直接传递给html的  所以不需要渲染模板和值对值的传递方式
            resp.send( result );

            if(error) throw error;
        } )
    } )
} )

//录入学生成绩
    routes.post('/add',(req,resp)=>{
        //难以提取原本传递过来的数据  那就将他们进行拆分
        var classid = req.body.classid;
        var adduid = req.body.uid;
        var addscores = req.body.scores;
        //console.log( classid , adduid , addscores)

    //    判断当前添加的数量是多人还是单人
    //    判断是否为数组  数组就是多人添加数据
    //    加载util模块   util.isArray( 数组 )   布尔型
        if( util.isArray( adduid ) ){
        //    多人添加
            var strs = '';
        //    循环学号
            adduid.forEach( (item , index)=>{
                // console.log(item , addscores[index])
            //    字符串拼接
                strs += `( ${item} , ${classid[index]} , ${addscores[index]} ),`
            } )
            console.log(strs)
            //截取字符串长度减一
            var str = strs.substring( 0 , strs.length - 1)
            //console.log(str)
        }else {
        //    单人添加
            var str = `( ${adduid} , ${classid} , ${addscores} )`
        }

    //    拼接sql语句
        var sql = `INSERT INTO scores ( uid , class_id , scores ) VALUES ${str}`;
        //console.log(sql)
        //    执行sql语句
        pool.getConnection( (err , connection)=>{
            if (err) throw err;
            connection.query( sql, (error , result)=>{
                //    释放连接
                connection.release();

                //添加成功重定向回查询成绩页
                if(result){
                    resp.redirect('/sel_scores')
                }

                if(error) throw error;
            } )
        } )
    })

//暴露接口
module.exports = routes;